-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE [aircond]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[AircondBackup]
	-- Add the parameters for the stored procedure here
@AirCondDatabases nvarchar(max),
@AirCondDirectory nvarchar(max) = NULL,
@AirCondBackupType nvarchar(max),
@AirCondVerify nvarchar(max) = 'N',
@AirCondCompress nvarchar(max) = NULL,
@AirCondCheckSum nvarchar(max) = 'N'

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	
    BEGIN TRY
		EXECUTE dbo.DatabaseBackup
		@Databases = @AirCondDatabases,
		@Directory = @AirCondDirectory,
		@BackupType = @AirCondBackupType,
		@Verify = @AirCondVerify,
		@CheckSum = @AirCondCheckSum,
		@Compress = @AirCondCompress
		
		DECLARE @id integer
		DECLARE db_cursor CURSOR FOR  

		select id from dbo.AUD_REVISION ar
		where DATEDIFF(DD, dateadd(hh, 8, DATEADD(ss, ar.timestamp/(1000), '1/1/1970')), SYSDATETIME()) > 180

		OPEN db_cursor   
		FETCH NEXT FROM db_cursor INTO @id

		WHILE @@FETCH_STATUS = 0   
		BEGIN   

			delete from dbo.BUILDING_AUD where REV = @id
			delete from dbo.FLOOR_AUD where REV = @id
			delete from dbo.FLOOR_PLAN_AUD where REV = @id
			delete from dbo.MACHINE_AUD where REV = @id
			delete from dbo.MData_AUD where REV = @id
			delete from dbo.SCHEDULE_TERM_AUD where REV = @id
			delete from dbo.USERDETAIL_USERGROUP_AUD where REV = @id

			FETCH NEXT FROM db_cursor INTO @id 
		END 
		
		delete from dbo.SData_AUD where DATEDIFF(DD, UPDATEDATE, SYSDATETIME()) > 180
		delete from dbo.AUD_REVISION where DATEDIFF(DD, dateadd(hh, 8, DATEADD(ss, timestamp/(1000), '1/1/1970')), SYSDATETIME()) > 180
		
    END TRY
    BEGIN CATCH
		--RAISERROR('Backup Error',16,1) WITH NOWAIT
		SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
	
END
GO
